package net.suncaper.mvc.mapper.impl;

import net.suncaper.mvc.mapper.OrderMapper;
import net.suncaper.mvc.model.domain.Order;
import net.suncaper.mvc.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Order表数据库访问接口实现类
 */
public class OrderMapperImpl implements OrderMapper {
    private static final QueryRunner RUNNER = new QueryRunner();

    /**
     * 获取用户所有订单
     *
     * @param userId 用户ID
     * @return 订单列表
     */
    @Override
    public List<Order> selectUserOrders(String userId) {
        // 修正字段名：UserId → user_id
        String sql = "SELECT * FROM `order` WHERE user_id = ?";
        Connection connection = JDBCUtils.getConnection();
        List<Order> orders = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Order order = new Order();
                order.setId(rs.getInt("id"));
                order.setCreateTime(rs.getTimestamp("create_time"));
                order.setPayTime(rs.getTimestamp("pay_time"));
                order.setCancelTime(rs.getTimestamp("cancel_time"));
                order.setAmount(rs.getBigDecimal("amount"));
                order.setPaymentMethod(rs.getString("payment_method"));
                order.setRoomType(rs.getString("room_type"));
                order.setHotelName(rs.getString("hotel_name"));
                order.setUserId(rs.getString("user_id"));
                orders.add(order);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return orders;
    }

    /**
     * 获取所有订单
     *
     * @return 订单列表
     */
    @Override
    public List<Order> selectAllOrders() {
        String sql = "SELECT * FROM `order`";
        Connection connection = JDBCUtils.getConnection();
        List<Order> orders = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Order order = new Order();
                order.setId(rs.getInt("id"));
                order.setCreateTime(rs.getTimestamp("create_time"));
                order.setPayTime(rs.getTimestamp("pay_time"));
                order.setCancelTime(rs.getTimestamp("cancel_time"));
                order.setAmount(rs.getBigDecimal("amount"));
                order.setPaymentMethod(rs.getString("payment_method"));
                order.setRoomType(rs.getString("room_type"));
                order.setHotelName(rs.getString("hotel_name"));
                order.setUserId(rs.getString("user_id"));
                orders.add(order);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return orders;
    }

    /**
     * 更新订单
     *
     * @param order 订单信息
     * @return 更新结果
     */
    @Override
    public boolean updateOrder(Order order) {
        // 修正字段名：统一为下划线命名，并调整参数顺序
        String sql = "UPDATE `order` SET create_time = ?, pay_time = ?, cancel_time = ?, amount = ?, payment_method = ?, room_type = ?, hotel_name = ? WHERE id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    order.getCreateTime(), order.getPayTime(), order.getCancelTime(),
                    order.getAmount(), order.getPaymentMethod(),
                    order.getRoomType(), order.getHotelName(), order.getId()
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 更新订单并返回更新前的订单信息
     */
    @Override
    public Order updateOrderAndReturnOriginal(Order order) {
        // 1. 先查询原订单信息
        String selectSql = "SELECT * FROM `order` WHERE id = ?";
        Connection connection = JDBCUtils.getConnection();
        Order originalOrder = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(selectSql);
            pstmt.setInt(1, order.getId());
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                originalOrder = new Order();
                originalOrder.setId(rs.getInt("id"));
                originalOrder.setCreateTime(rs.getTimestamp("create_time"));
                originalOrder.setPayTime(rs.getTimestamp("pay_time"));
                originalOrder.setCancelTime(rs.getTimestamp("cancel_time"));
                originalOrder.setAmount(rs.getBigDecimal("amount"));
                originalOrder.setPaymentMethod(rs.getString("payment_method"));
                originalOrder.setRoomType(rs.getString("room_type"));
                originalOrder.setHotelName(rs.getString("hotel_name"));
                originalOrder.setUserId(rs.getString("user_id"));
            } else {
                return null; // 订单不存在
            }
            
            closeResources(rs, pstmt);

            // 2. 执行更新操作（修正字段名：CancelTime → cancel_time）
            String updateSql = "UPDATE `order` SET cancel_time = ? WHERE id = ?";
            pstmt = connection.prepareStatement(updateSql);
            if (order.getCancelTime() != null) {
                pstmt.setTimestamp(1, new Timestamp(order.getCancelTime().getTime()));
            } else {
                pstmt.setNull(1, Types.TIMESTAMP);
            }
            pstmt.setInt(2, order.getId());
            pstmt.executeUpdate();
            
        } catch (SQLException e) {
            throw new RuntimeException("更新订单失败：" + e.getMessage());
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return originalOrder;
    }

    /**
     * 根据酒店名称获取订单
     *
     * @param hotelName 酒店名称
     * @return 订单列表
     */
    @Override
    public List<Order> selectOrdersByHotel(String hotelName) {
        // 修正字段名：HotelName → hotel_name
        String sql = "SELECT * FROM `order` WHERE hotel_name = ?";
        Connection connection = JDBCUtils.getConnection();
        List<Order> orders = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, hotelName);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Order order = new Order();
                order.setId(rs.getInt("id"));
                order.setCreateTime(rs.getTimestamp("create_time"));
                order.setPayTime(rs.getTimestamp("pay_time"));
                order.setCancelTime(rs.getTimestamp("cancel_time"));
                order.setAmount(rs.getBigDecimal("amount"));
                order.setPaymentMethod(rs.getString("payment_method"));
                order.setRoomType(rs.getString("room_type"));
                order.setHotelName(rs.getString("hotel_name"));
                order.setUserId(rs.getString("user_id"));
                orders.add(order);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return orders;
    }

    @Override
    public int updateOrderForPayment(Integer id, String paymentMethod, BigDecimal amount, java.util.Date payTime) {
        String sql = "UPDATE `order` SET pay_time = ?, amount = ?, payment_method = ? WHERE id = ? AND pay_time IS NULL";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        PreparedStatement pstmt = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            if (payTime != null) {
                pstmt.setTimestamp(1, new Timestamp(payTime.getTime()));
            } else {
                pstmt.setNull(1, Types.TIMESTAMP);
            }
            pstmt.setBigDecimal(2, amount);
            pstmt.setString(3, paymentMethod);
            pstmt.setInt(4, id);
            
            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(null, pstmt);
            JDBCUtils.close();
        }
        return rows;
    }

    @Override
    public boolean deleteOrder(Integer orderId) {
        String sql = "DELETE FROM `order` WHERE id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(connection, sql, orderId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 创建订单并获取自增ID
     *
     * @param order 订单信息
     */
    @Override
    public void insertOrderAndReturnId(Order order) {
        // 修正字段名：统一为下划线命名，调整参数顺序与表结构一致
        String sql = "INSERT INTO `order`(create_time, pay_time, cancel_time, amount, payment_method, room_type, hotel_name, user_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        try {
            // 执行插入并获取自增ID
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setTimestamp(1, new Timestamp(order.getCreateTime().getTime()));
            
            if (order.getPayTime() != null) {
                ps.setTimestamp(2, new Timestamp(order.getPayTime().getTime()));
            } else {
                ps.setNull(2, Types.TIMESTAMP);
            }
            
            if (order.getCancelTime() != null) {
                ps.setTimestamp(3, new Timestamp(order.getCancelTime().getTime()));
            } else {
                ps.setNull(3, Types.TIMESTAMP);
            }
            
            ps.setBigDecimal(4, order.getAmount());
            ps.setString(5, order.getPaymentMethod());
            ps.setString(6, order.getRoomType());
            ps.setString(7, order.getHotelName());
            ps.setString(8, order.getUserId());

            ps.executeUpdate();

            // 提取自增ID并设置到order对象中
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                order.setId(rs.getInt(1));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, ps);
            JDBCUtils.close();
        }
    }
    
    /**
     * 根据订单ID获取订单详情
     *
     * @param orderId 订单ID
     * @return 订单对象（包含房型和酒店名等信息）
     */
    @Override
    public Order selectOrderById(Integer orderId) {
        String sql = "SELECT * FROM `order` WHERE id = ?";
        Connection connection = JDBCUtils.getConnection();
        Order order = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, orderId);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                order = new Order();
                order.setId(rs.getInt("id"));
                order.setCreateTime(rs.getTimestamp("create_time"));
                order.setPayTime(rs.getTimestamp("pay_time"));
                order.setCancelTime(rs.getTimestamp("cancel_time"));
                order.setAmount(rs.getBigDecimal("amount"));
                order.setPaymentMethod(rs.getString("payment_method"));
                order.setRoomType(rs.getString("room_type"));
                order.setHotelName(rs.getString("hotel_name"));
                order.setUserId(rs.getString("user_id"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return order;
    }
    
    /**
     * 关闭资源
     * 
     * @param rs ResultSet对象
     * @param stmt PreparedStatement对象
     */
    private void closeResources(ResultSet rs, PreparedStatement stmt) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}